﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LibUtilities.dal;
using System.Data;
using System.Data.SqlClient;

namespace CLibArtreve.dal
{
    public class ArtDAL : SqlDatabase
    {
        #region CONSTRUCTORS
        public ArtDAL(string connectionString) : base(connectionString) { }
        #endregion

        #region OVERRIDE METHODS
        public override string ToString()
        {
            return "DATA ACCES LAYER: " + this.GetType().ToString();
        }
        #endregion

        #region GET METHODS
        public DataSet GetAllArts()
        {
            try
            {
                return ExecuteDataSetWithCommonQuery(Queries.SELECTALLARTS, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataSet GetAllArtsWithCategories()
        {
            try
            {
                return ExecuteDataSetWithCommonQuery(Queries.SELECTALLARTSWITHCATEGORIES, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataSet GetAllSpotlightedArts()
        {
            try
            {
                // MAKE A LIST OF PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                // MAKE PARAMETER
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_spotlighted";
                parameter.Value = 1;
                parameters.Add(parameter);

                // EXECUTE
                return ExecuteDataSetWithCommonQuery(Queries.SELECTALLSPOTLIGHTEDARTS, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataSet GetArtById(Int64? id)
        {
            try
            {
                // MAKE A LIST OF PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                // MAKE PARAMETER
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_id";
                parameter.Value = id;
                parameters.Add(parameter);

                // EXECUTE
                return ExecuteDataSetWithCommonQuery(Queries.SELECTARTBYID, parameters);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataSet GetArtByUserId(Int64? id)
        {
            try
            {
                // MAKE A LIST OF PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                // MAKE PARAMETER
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_id";
                parameter.Value = id;
                parameters.Add(parameter);

                // EXECUTE
                return ExecuteDataSetWithCommonQuery(Queries.SELECTALLARTBYUSERID, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataSet GetDeletedArtByUserId(Int64? id)
        {
            try
            {
                // MAKE A LIST OF PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                // MAKE PARAMETER
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_id";
                parameter.Value = id;
                parameters.Add(parameter);

                // EXECUTE
                return ExecuteDataSetWithCommonQuery(Queries.SELECTALLDELETEDARTBYUSERID, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }   
        public DataSet GetArtByCategoryId(Int64 id)
        {
            try
            {
                // MAKE A LIST OF PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                // MAKE PARAMETER
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "categories_category_id";
                parameter.Value = id;
                parameters.Add(parameter);

                // EXECUTE
                return ExecuteDataSetWithCommonQuery(Queries.SELECTARTSBYCATEGORYID, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataSet GetArtForArtboardByUserId(Int64 id)
        {
            try
            {
                // MAKE A LIST OF PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                // MAKE PARAMETER
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "user_id";
                parameter.Value = id;
                parameters.Add(parameter);

                // EXECUTE
                return ExecuteDataSetWithCommonQuery(Queries.SELECTARTSFORARTBOARDSBYUSERID, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region INSERT METHODS
        public int InsertArt(string artName, string artDescription, string artSize, Int64 artPrice,
            Int64 artNumberOf, DateTime? artModificationdate, Boolean artSpotlighted,
            Int64 artpicturesArtpictureId, Int64? usersUserId)
        {
            try
            {
                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                //MAKE PARAMETERS
                SqlParameter parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_name";
                parameter.Value = artName;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_description";
                parameter.Value = artDescription;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_size";
                parameter.Value = artSize;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_price";
                parameter.Value = artPrice;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_numberof";
                parameter.Value = artNumberOf;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.DateTime;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_creationdate";
                parameter.Value = DateTime.Now;
                parameters.Add(parameter);

                /*if (artModificationdate != null)
                {
                    parameter = new SqlParameter();
                    parameter.DbType = DbType.DateTime;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.ParameterName = "art_modificationdate";
                    parameter.Value = artModificationdate;
                    parameters.Add(parameter);
                }
                else
                {
                    parameter = new SqlParameter();
                    parameter.DbType = DbType.DateTime;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.ParameterName = "art_modificationdate";
                    parameter.Value = DBNull.Value;
                    parameters.Add(parameter);
                }*/

                parameter = new SqlParameter();
                parameter.DbType = DbType.Boolean;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_spotlighted";
                parameter.Value = artSpotlighted;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "artpictures_artpicture_id";
                parameter.Value = artpicturesArtpictureId;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "users_user_id";
                parameter.Value = usersUserId;
                parameters.Add(parameter);

                //EXECUTE
                int p_art = ExecuteNonQueryWithCommonQuery(Queries.INSERTART, parameters, true);
                if (p_art > 0)
                {
                    p_art = Convert.ToInt32(ExecuteScalarWithCommonQuery<decimal>(Queries.SELECTIDENTITYAFTERINSERTSQLSERVER));
                }
                return p_art;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region UPDATE METHODS
        public int UpdateArt(Int64? artId, string artName, string artDescription, string artSize, Int64 artPrice,
            Int64 artNumberOf, DateTime? artModificationdate, Boolean artSpotlighted,
            Int64 artpicturesArtpictureId, Int64? usersUserId)
        {
            try
            {
                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                SqlParameter parameter = new SqlParameter();
                if (artId != null)
                {
                    parameter = new SqlParameter();
                    parameter.DbType = DbType.Int64;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.ParameterName = "art_id";
                    parameter.Value = artId;
                    parameters.Add(parameter);
                }
                else
                {
                    parameter = new SqlParameter();
                    parameter.DbType = DbType.Int64;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.ParameterName = "art_id";
                    parameter.Value = DBNull.Value;
                    parameters.Add(parameter);
                }

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_name";
                parameter.Value = artName;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_description";
                parameter.Value = artDescription;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.String;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_size";
                parameter.Value = artSize;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_price";
                parameter.Value = artPrice;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_numberof";
                parameter.Value = artNumberOf;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.DateTime;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_creationdate";
                parameter.Value = DateTime.Now;
                parameters.Add(parameter);

                /*if (artDeleteddate != null)
                {
                    parameter = new SqlParameter();
                    parameter.DbType = DbType.DateTime;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.ParameterName = "art_modificationdate";
                    parameter.Value = artModificationdate;
                    parameters.Add(parameter);
                }
                else
                {
                    parameter = new SqlParameter();
                    parameter.DbType = DbType.DateTime;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.ParameterName = "art_modificationdate";
                    parameter.Value = DBNull.Value;
                    parameters.Add(parameter);
                }*/

                /*if (artModificationdate != null)
                {
                    parameter = new SqlParameter();
                    parameter.DbType = DbType.DateTime;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.ParameterName = "art_modificationdate";
                    parameter.Value = artModificationdate;
                    parameters.Add(parameter);
                }
                else
                {
                    parameter = new SqlParameter();
                    parameter.DbType = DbType.DateTime;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.ParameterName = "art_modificationdate";
                    parameter.Value = DBNull.Value;
                    parameters.Add(parameter);
                }*/

                parameter = new SqlParameter();
                parameter.DbType = DbType.Boolean;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_spotlighted";
                parameter.Value = artSpotlighted;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "artpictures_artpicture_id";
                parameter.Value = artpicturesArtpictureId;
                parameters.Add(parameter);

                parameter = new SqlParameter();
                parameter.DbType = DbType.Int64;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "users_user_id";
                parameter.Value = usersUserId;
                parameters.Add(parameter);

                return ExecuteNonQueryWithCommonQuery(Queries.UPDATEART, parameters, true);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        public int UpdateArtDeletedDate(Int64? artId, DateTime? artDeleteddate)
        {
            try
            {
                //MAKE SOME PARAMETERS
                List<SqlParameter> parameters = new List<SqlParameter>();

                SqlParameter parameter = new SqlParameter();
                if (artId != null)
                {
                    parameter = new SqlParameter();
                    parameter.DbType = DbType.Int64;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.ParameterName = "art_id";
                    parameter.Value = artId;
                    parameters.Add(parameter);
                }
                else
                {
                    throw new Exception("Cannot delete Art without its ID.");
                    /*
                    parameter = new SqlParameter();
                    parameter.DbType = DbType.Int64;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.ParameterName = "art_id";
                    parameter.Value = DBNull.Value;
                    parameters.Add(parameter);
                    */
                }

                parameter = new SqlParameter();
                parameter.DbType = DbType.DateTime;
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "art_deleteddate";
                parameter.Value = artDeleteddate;
                parameters.Add(parameter);

                return ExecuteNonQueryWithCommonQuery(Queries.UPDATEARTDELETEDATE, parameters, true);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }       
        
        #endregion
        
    }
}
